#!/usr/bin/ruby -v
# this script only worked with hpricot versions < 0.5
# hpricot has since changed considerably and so this script would have to be reworked, but it shouldn't be too daunting of a task.
require 'rubygems'
require 'hpricot'
require 'mysql'
require 'open-uri'

# connect to mysql:
m = Mysql.real_connect("localhost", "root", "landlove", "state_md")
# grab only the relevant account numbers:
res = m.query("SELECT parcel_id FROM sdat WHERE scrape_status!='Success'")

# now we start our loop for each account number:
res.each_hash do |row|
  parcel_id = row["parcel_id"]
    if parcel_id[0,2] == '02'
      acct = parcel_id[2,2] + '+' + parcel_id[4,3] + '+' + parcel_id[7,8]
    elsif parcel_id[0,2] == '03'
      acct = parcel_id[2,2] + '+' + parcel_id[4,2] + '+' + parcel_id[6,5].rstrip + '+' + parcel_id[11,5].rstrip
   else
      acct = parcel_id[2,2] + '+' + parcel_id[4,12]
   end

# put together the url query string:
url = "http://sdatcert3.resiusa.org/rp_rewrite/detail.asp?accountnumber=" + acct + "&county=" + parcel_id[0,2] + "&intMenu=2&SearchType=Account"

# begin rescue loop:
  begin
    html = Hpricot(open(url, "User-Agent" => "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; YPC 3.0.1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)", "Accept-Encoding" => "gzip"))
    if "Real Property Search - Individual Report" == html.search("/head/title/text()").inner_text
      m.query("UPDATE sdat SET
      owner_name1 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[1]/td[1]/font/text()")[0].inner_text)}',
      owner_name2 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[1]/td[1]/font/text()")[1].inner_text.sub(/\r\n\s*/, ''))}',
      property_use = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[1]/td[3]/font/text()").inner_text.sub(/\s\r\n\s*/, ''))}',
      principle_residence = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[2]/td[3]/font/text()").inner_text)}',
      mailing_address1 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[3]/td[1]/font/text()").inner_text)}',
      mailing_address2 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[4]/td[1]/font/text()").inner_text.sub(/\s\s+/, ', '))}',
      deed_reference1 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[3]/td[3]/font/text()").inner_text.sub(/1\)\s\r\n\s+/, ' '))}',
      deed_reference2 = '#{m.escape_string(html.search("/body/center/center/table[0]/tr[4]/td[3]/font/text()").inner_text.sub(/2\)\s\r\n\s+/, ' '))}',
      premises_address1 = '#{m.escape_string(html.search("/body/center/center/center[0]/table[0]/tr[2]/td[0]/font/text()").inner_text)}',
      premises_address2 = '#{m.escape_string(html.search("/body/center/center/center[0]/table[0]/tr[3]/td[0]/font/text()").inner_text.sub(/\s\s/, ', MD '))}',
      legal_description1 = '#{m.escape_string(html.search("/body/center/center/center[0]/table[0]/tr[2]/td[2]/font/text()").inner_text)}',
      legal_description2 = '#{m.escape_string(html.search("/body/center/center/center[0]/table[0]/tr[3]/td[2]/font/text()").inner_text.sub(/\s\s+/, ' '))}',
      legal_description3 = '#{m.escape_string(html.search("/body/center/center/center[0]/table[0]/tr[4]/td[2]/font/text()").inner_text.sub(/\s\s+/, ' '))}',
      map = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[0]/font/text()").inner_text)}',
      grid = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[1]/font/text()").inner_text)}',
      parcel = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[2]/font/text()").inner_text)}',
      sub_district = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[3]/font/text()").inner_text)}',
      subdivision = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[4]/font/text()").inner_text)}',
      section = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[5]/font/text()").inner_text)}',
      block = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[6]/font/text()").inner_text)}',
      lot = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[7]/font/text()").inner_text)}',
      assessment_area = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[8]/font/text()").inner_text)}',
      plat_no = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[0]/td[10]/font/text()").inner_text)}',
      plat_ref = '#{m.escape_string(html.search("/body/center/center/center[0]/table[1]/tr[1]/td[10]/font/text()").inner_text)}',
      town = '#{m.escape_string(html.search("/body/center/center/center[0]/table[2]/tr[0]/td[2]/font/text()").inner_text)}',
      ad_valorem = '#{m.escape_string(html.search("/body/center/center/center[0]/table[2]/tr[1]/td[1]/font/text()").inner_text)}',
      tax_class = '#{m.escape_string(html.search("/body/center/center/center[0]/table[2]/tr[2]/td[1]/font/text()").inner_text)}',
      primary_structure_built = '#{m.escape_string(html.search("/body/center/center/center[0]/table[3]/tr[1]/td[0]/div/font/text()").inner_text)}',
      enclosed_area = '#{m.escape_string(html.search("/body/center/center/center[0]/table[3]/tr[1]/td[1]/div/font/text()").inner_text.sub(/\s\r\n\s+/, ' ').sub(/<AM \w+\$>/, ''))}',
      property_land_area = '#{m.escape_string(html.search("/body/center/center/center[0]/table[3]/tr[1]/td[2]/div/font/text()").inner_text.sub(/\s+<AM \w+\$>/, ''))}',
      county_use = '#{m.escape_string(html.search("/body/center/center/center[0]/table[3]/tr[1]/td[3]/div/font/text()").inner_text)}',
      stories = '#{m.escape_string(html.search("/body/center/center/center[0]/table[4]/tr[1]/td[0]/font/text()").inner_text)}',
      basement = '#{m.escape_string(html.search("/body/center/center/center[0]/table[4]/tr[1]/td[1]/font/text()").inner_text)}',
      structure_type = '#{m.escape_string(html.search("/body/center/center/center[0]/table[4]/tr[1]/td[2]/font/text()").inner_text)}',
      exterior = '#{m.escape_string(html.search("/body/center/center/center[0]/table[4]/tr[1]/td[3]/font/text()").inner_text)}',
      base_value_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[1]/font/text()")[0].inner_text.sub(/\r\n\s*/, ''))}',
      base_value_improvements = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[1]/font/text()")[1].inner_text.sub(/\r\n\s*/, ''))}',
      base_value_total = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[1]/font/text()")[2].inner_text.sub(/\r\n\s*/, ''))}',
      base_value_preferential_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[1]/font/text()")[3].inner_text.sub(/\r\n\s*/, ''))}',
      value_as_of_date = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[2]/td[1]/font/text()")[1].inner_text.sub(/\r\n\s*/, ''))}',
      value_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[2]/font/text()")[0].inner_text.sub(/\s\r\n\s+/, ''))}',
      value_improvements = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[2]/font/text()")[1].inner_text.sub(/\r\n\s+/, ''))}',
      value_total = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[2]/font/text()")[2].inner_text.sub(/\r\n\s+/, ''))}',
      value_preferential_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[2]/font/text()")[3].inner_text.sub(/\r\n\s+/, ''))}',
      phase_in_as_of_date1 = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[2]/td[2]/font/text()")[1].inner_text.sub(/\r\n\s*/, ''))}',
      phase_in1_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[3]/font/text()")[0].inner_text.sub(/\s\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in1_improvements = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[3]/font/text()")[1].inner_text.sub(/\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in1_total = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[3]/font/text()")[2].inner_text.sub(/\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in1_preferential_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[3]/font/text()")[3].inner_text.sub(/\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in_as_of_date2 = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[2]/td[3]/font/text()")[1].inner_text.sub(/\r\n\s*/, ''))}',
      phase_in2_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[4]/font/text()")[0].inner_text.sub(/\s\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in2_improvements = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[4]/font/text()")[1].inner_text.sub(/\r\n\s*/, '').sub(/<AM \w+\$>/, ''))}',
      phase_in2_total = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[4]/font/text()")[2].inner_text.sub(/\r\n\s*/, ''))}',
      phase_in2_preferential_land = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[3]/td[4]/font/text()")[3].inner_text.sub(/\r\n\s*/, ''))}',
      seller1 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[1]/td[1]/font/text()").inner_text)}',
      sell_type1 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[2]/td[1]/font/text()").inner_text)}',
      sell_price1 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[1]/td[5]/font/text()").inner_text)}',
      sell_date1 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[1]/td[3]/font/text()").inner_text)}',
      sell1_deed1 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[2]/td[3]/font/text()").inner_text)}',
      sell1_deed2 = '#{m.escape_string(html.search("/body/center/center/table[4]/tr[2]/td[5]/font/text()").inner_text)}',
      seller2 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[0]/td[1]/font/text()").inner_text)}',
      sell_date2 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[0]/td[3]/font/text()").inner_text)}',
      sell_type2 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[1]/td[1]/font/text()").inner_text)}',
      sell_price2 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[0]/td[5]/font/text()").inner_text)}',
      sell2_deed1 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[1]/td[3]/font/text()").inner_text)}',
      sell2_deed2 = '#{m.escape_string(html.search("/body/center/center/table[5]/tr[1]/td[5]/font/text()").inner_text)}',
      seller3 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[0]/td[1]/font/text()").inner_text)}',
      sell_date3 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[0]/td[3]/font/text()").inner_text)}',
      sell_type3 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[1]/td[1]/font/text()").inner_text)}',
      sell_price3 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[0]/td[5]/font/text()").inner_text)}',
      sell3_deed1 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[1]/td[3]/font/text()").inner_text)}',
      sell3_deed2 = '#{m.escape_string(html.search("/body/center/center/table[6]/tr[1]/td[5]/font/text()").inner_text)}',
      county_class = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[2]/td[1]/font/text()").inner_text)}',
      state_class = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[3]/td[1]/font/text()").inner_text)}',
      municipal_class = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[4]/td[1]/font/text()").inner_text)}',
      exempt_date1 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[1]/td[2]/font/text()").inner_text)}',
      county_exempt_date1 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[2]/td[2]/font/text()").inner_text)}',
      state_exempt_date1 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[3]/td[2]/font/text()").inner_text)}',
      municipal_exempt_date1 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[4]/td[2]/font/text()").inner_text)}',
      exempt_date2 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[1]/td[3]/font/text()").inner_text)}',
      county_exempt_date2 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[2]/td[3]/font/text()").inner_text)}',
      state_exempt_date2 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[3]/td[3]/font/text()").inner_text)}',
      municipal_exempt_date2 = '#{m.escape_string(html.search("/body/center/center/table[8]/tr[4]/td[3]/font/text()").inner_text)}',
      tax_exempt = '#{m.escape_string(html.search("/body/center/center/table[9]/tr[1]/td[1]/font/text()").inner_text.sub(/<AM .+\$>/, ''))}',
      exempt_class = '#{m.escape_string(html.search("/body/center/center/table[9]/tr[2]/td[1]/font/text()").inner_text.sub(/<AM.+\$>/, ''))}',
      special_tax_recapture1 = '#{m.escape_string(html.search("/body/center/center/table[9]/tr[2]/td[2]/font/text()").inner_text)}',
      special_tax_recapture2 = '#{m.escape_string(html.search("/body/center/center/table[9]/tr[3]/td[2]/font/text()").inner_text.gsub(/\s*\*\s*/,''))}',
      account_notes = '#{m.escape_string(html.search("/body/center/table[1]/tr[0]/td[0]/font/b/text()").inner_text.gsub(/\s*\*\s*\*\s*/, ''))}',
      value_info_notes = '#{m.escape_string(html.search("/body/center/center/center[1]/table/tr[0]/td[0]/td[1]/font").inner_text.gsub(/\r\n\s+/, ' ').sub(/\s+&nbsp;/, ''))}',
      scrape_status = 'Success'
      WHERE parcel_id='#{parcel_id}'")
    else
      # otherwise, there must be something malformed about the html returned, so mark parcel with error:
      m.query("UPDATE sdat SET scrape_status = 'Error' WHERE parcel_id='#{parcel_id}'")
    end
  rescue OpenURI::HTTPError, Timeout::Error, Errno::ECONNRESET => error
    error = error.to_s
    m.query("UPDATE sdat SET scrape_status = '#{error}' WHERE parcel_id='#{parcel_id}'")
  end
end

m.close
